package net.ojbk.saishi.dao;

import java.sql.ResultSet;


import java.sql.SQLException;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.saishi.po.syMatchPart;

public class syMatchPartDao {
	static final String tableName="syMatchPart";
	DatabaseConnection dbconn;
	Statement stmt;
	public syMatchPartDao(DatabaseConnection dbconn) {
		this.dbconn = dbconn;
		stmt=dbconn.getStatement();
	}
	
	
	/**
	 * 插入 一条参赛记录 将会用作赛事参加
	 * 
	 * @param symp Po层 syMatchPart 对象
	 * @return
	 */
	public boolean addOneRecord(syMatchPart symp){ // is OK? ：yes
		if (symp == null) return false; // sym 不能位空
		boolean flag=false;
		try {
			String sql="insert into "
					+ tableName
					+ "(userId,matchId,partTime) value('"
					+symp.getUserId()
					+"','"
					+symp.getMatchId()
					+ "','"
					+ new java.sql.Timestamp(symp.getPartTime().getTime())
					+ "')";
			System.out.println("sql：" + sql);
			stmt.executeUpdate(sql);
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return flag;
	}
	
	/**
	 * 描述：删除一条参赛记录，用于取消报名 或 撤销赛事时同步删除参赛信息
	 * 要求：
	 * 1、mtachId userId 为主码a
	 * @param ud
	 * @return
	 */
	public boolean deleteOneRecord(syMatchPart symp) { // is OK? ：yes
		if (symp == null) return false;
		if (symp.getMatchId() == null || symp.getUserId() == null) return false; // 主码不能为空
		boolean flag = false;
		try {
			String sql = "DELETE FROM "
					+ tableName
					+ " WHERE userId='"
					+ symp.getUserId()
					+ "' AND matchId='"
					+ symp.getMatchId()
					+ "';";
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
//	/**
//	 * 跟新一条记录 一般用于 重置比赛结束时间、赛事密码等。
//	 * 1、传入 主码不能为空
//	 * 
//	 * @param sym
//	 * @return
//	 */
//	public boolean updateOneRecord(syMatch sym) { // is OK? ：no
//		if (sym == null) return false;
//		if (sym.getMatchId() == null) return false; // 主码不能为空
//		boolean flag = false;
//		try {
//			// UPDATE userData SET id='10001', email='200562421@qq.com', tel='12345678901', idcard='362226199711200017', grade='2016', college='软件学院', clas='软件2016-2', registrationTime='2018.3.29', lastIP='192.168.1.1' WHERE id='10001';
//			String sql = "UPDATE "
//					+ tableName
//					+ " SET "
//					+ ""
//					+ ""
//					+ ""
//					+ ";";
//			stmt.executeUpdate(sql);
//			return true;
//		} catch(Exception e) {
//			e.printStackTrace();
//		}
//		return flag;
//	}
	
	/**
	 * 查询一条数据
	 * 1、能够 根据 symp.matchId 、symp.userId 查询
	 * 2、传入 主码为null 返回 false
	 * 3、传入 对象为null 返回 false
	 * 
	 * @param sym
	 * @return
	 */
	public boolean queryOneRecord(syMatchPart symp) { // is OK? ：yes
		if (symp == null) return false;
		if (symp.getMatchId() == null || symp.getUserId() == null) return false; // 主码不能为空
		boolean flag = false;
		ResultSet re;
		try {
			//select * from userData where id='76670' or email='1531764720@qq.com'
			String sql="select * from "
					+ tableName
					+ " where userId='"
					+symp.getUserId()
					+"'or matchId='"
					+symp.getMatchId()
					+"';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				symp.setMatchId(re.getString("userId"));
				symp.setUserId(re.getString("matchId"));
				symp.setPartTime(re.getTimestamp("partTime"));
//				ud.setId(re.getString("id"));
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	

	public int getCount() { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public int getCountByUserId(String userId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public int getCountByMatchId(String matchId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where matchId='"
					+ matchId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
//	/**
//	 * 如果 count=0 则 syms.lengh=0 不影响
//	 * @return
//	 */
//	public syMatch[] queryAllRecod(){ // is OK? ：no
//		int count = this.getCount();
//		syMatch[] syms=new syMatch[count]; 
//		ResultSet re;
//		syMatch sym;
//		try {
//			// SELECT * FROM userData
//			String sql="SELECT * FROM "
//					+ tableName
//					+ ";";
//			re = stmt.executeQuery(sql); 
//			for (int i=0; re.next(); i++) {
//				sym = new syMatch();
//				
//				
//				
//				syms[i] = sym;
//			}
//		} catch(Exception e) {
//			e.printStackTrace();
//			return syms;
//		}
//		return syms;
//	}
	
	public syMatchPart[] queryAllRecodByUserId(String userId){ // is OK? ：yes
		int count = this.getCountByUserId(userId);
		syMatchPart[] symps=new syMatchPart[count]; 
		ResultSet re;
		syMatchPart symp;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				symp = new syMatchPart();
				
				// 赋值代码
				symp.setMatchId(re.getString("matchId"));
				symp.setUserId(re.getString("userId"));
				symp.setPartTime(re.getTimestamp("partTime"));
				
				symps[i] = symp;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return symps;
		}
		return symps;
	}
	
	public syMatchPart[] queryAllRecodByMatchId(String matchId){ // is OK? ：yes
		int count = this.getCountByMatchId(matchId);
		syMatchPart[] symps=new syMatchPart[count]; 
		ResultSet re;
		syMatchPart symp;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where matchId='"
					+ matchId
					+ "';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				symp = new syMatchPart();
				
				// 赋值代码
				symp.setMatchId(re.getString("matchId"));
				symp.setUserId(re.getString("userId"));
				
				symps[i] = symp;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return symps;
		}
		return symps;
	}
	
	public boolean checkOneRecord(String matchId, String userId) { // is OK? ：yes
		boolean flag = false;
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where matchId='"
					+ matchId
					+ "' AND userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
}
